﻿using DocumentFormat.OpenXml.Packaging;
using OpenXml.Excel.Contract;
using OpenXml.Excel.Data;
using System;
using System.Collections.Generic;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text.RegularExpressions;
using System.Xml;
using System.Xml.Linq;

namespace OpenXml.Excel
{
    /// <summary>
    /// OPEN xml扩展类
    /// </summary>
    public static class OpenXmlExtessions
    {
        const string xml_namespace = "http://schemas.openxmlformats.org/spreadsheetml/2006/main";
        const string xml_relation_namespace = "http://schemas.openxmlformats.org/officeDocument/2006/relationships";

        /// <summary>
        /// 返回sheet 数据集合
        /// </summary>
        /// <param name="workBookPart"></param>
        /// <returns></returns>
        public static List<T> GetSheetDatas<T>(this WorkbookPart workBookPart)
        {
            if (workBookPart.Workbook == null)
            {
                throw new Exception("Excel中没有数据!");
            }

            XDocument doc1 = XDocument.Load(new MemoryStream(System.Text.Encoding.UTF8.GetBytes(workBookPart.Workbook.OuterXml)));


            var sheets = doc1.Root
                .Elements(XName.Get("sheets", xml_namespace))
                .Elements(XName.Get("sheet", xml_namespace))
                .Select(a => new
                {
                    itemId = a.Attribute("name").Value,
                    sheetId = a.Attribute(XName.Get("id", xml_relation_namespace)).Value
                })
                .ToDictionary(a => a.itemId, a => a.sheetId);

            var type = typeof(T);
            var headerAttribute = type.GetCustomAttribute<HeadAttribute>();

            if (headerAttribute == null)
            {
                throw new Exception("泛型 T 必须包含 SheetNameAttribute 属性");
            }

            string sheetName = headerAttribute.SheetName;

            //所有的共有字符串集合
            var strings = workBookPart.SharedStringTablePart.GetShareString();

            WorksheetPart worksheetPart = workBookPart.GetPartById(sheets[sheetName]) as WorksheetPart;

            return worksheetPart.GetSheetData<T>(headerAttribute.RowId, strings);
        }


        /// <summary>
        /// 获取指定sheet 的 数据
        /// </summary>
        /// <param name="workSheetPart">工作表对象</param>
        /// <param name="headRowId">表头所在行</param>
        /// <param name="sharedString">共享字符串</param>
        /// <returns></returns>
        public static List<T> GetSheetData<T>(this WorksheetPart workSheetPart, int headRowId, List<string> sharedString)
        {
            if (workSheetPart == null)
                return null;

            XNamespace n = xml_namespace;

            //所有单元格信息
            Dictionary<string, CellData> keyValuePairs = new Dictionary<string, CellData>();

            //表头信息键值对， 基于单元格文本存储
            Dictionary<string, CellData> headerKeyValuePairs = new Dictionary<string, CellData>();

            #region 读取工作表中文本相关内容
            var cellDatas = (from a in workSheetPart.GetXDocument().Root.Element(n + "sheetData").Elements(n + "row")
                             from b in a.Elements(n + "c")
                             let rowId = a.Attribute("r").Value
                             let columnId = CellData.GetCoumnId(b.Attribute("r").Value)
                             let isShared = b.Attribute("t") != null && b.Attribute("t").Value == "s"  /*t 属性可能不存在*/
                             let v = b.Element(n + "v")  /* v节点可能不存在*/
                             where v != null
                             select new CellData
                             {
                                 RowId = Convert.ToInt32(rowId),
                                 ColumnId = columnId,
                                 DataType = DataType.TextContent,
                                 Value = isShared ? sharedString[Convert.ToInt32(v.Value)] : v.Value
                             });


            foreach (var cell in cellDatas)
            {
                string key = cell.GetKey();

                if (!keyValuePairs.ContainsKey(key))
                {
                    keyValuePairs.Add(key, cell);
                }

                //表头信息键值对， 基于单元格文本存储
                if (cell.RowId == headRowId
                    && !headerKeyValuePairs.ContainsKey(cell.Value))
                {
                    headerKeyValuePairs.Add(cell.Value, cell);
                }
            }
            #endregion

            #region 读取工作表中图片相关内容
            //图片
            if (workSheetPart.DrawingsPart != null)
            {
                // : 会报异常
                string xml = workSheetPart.DrawingsPart.GetXDocument().Root.ToString().Replace(":", string.Empty);
                /*  可能会有bug*/
                XDocument doc = XDocument.Load(new System.IO.MemoryStream(System.Text.Encoding.UTF8.GetBytes(xml)));
                var imgCellDatas = (from a in doc.Root.Elements("xdrtwoCellAnchor")
                                        /* 位置为 起始 位置 +1*/
                                    let columnId = Convert.ToInt32(a.Element("xdrfrom").Element("xdrcol").Value) + 1
                                    /* 位置为 起始 位置 +1*/
                                    let rowId = Convert.ToInt32(a.Element("xdrfrom").Element("xdrrow").Value) + 1
                                    /* 1px:9525 excel */
                                    let width = Convert.ToInt32(a.Element("xdrpic").Element("xdrspPr").Element("axfrm").Element("aext").Attribute("cx").Value) / 9525
                                    let height = Convert.ToInt32(a.Element("xdrpic").Element("xdrspPr").Element("axfrm").Element("aext").Attribute("cy").Value) / 9525
                                    let id = a.Element("xdrpic").Element("xdrblipFill").Element("ablip").Attribute("rembed").Value
                                    let name = a.Element("xdrpic").Element("xdrnvPicPr").Element("xdrcNvPr").Attribute("name").Value
                                    select new CellData
                                    {
                                        RowId = rowId,
                                        ColumnId = columnId,
                                        Width = width,
                                        Height = height,
                                        Id = id,
                                        Value = name,
                                        DataType = DataType.Picture
                                    });


                var imgSingleDatas = (from a in doc.Root.Elements("xdroneCellAnchor")
                                          /* 位置为 起始 位置 +1*/
                                      let columnId = Convert.ToInt32(a.Element("xdrfrom").Element("xdrcol").Value) + 1
                                      /* 位置为 起始 位置 +1*/
                                      let rowId = Convert.ToInt32(a.Element("xdrfrom").Element("xdrrow").Value) + 1
                                      /* 1px:9525 excel */
                                      let width = Convert.ToInt32(a.Element("xdrpic").Element("xdrspPr").Element("axfrm").Element("aext").Attribute("cx").Value) / 9525
                                      let height = Convert.ToInt32(a.Element("xdrpic").Element("xdrspPr").Element("axfrm").Element("aext").Attribute("cy").Value) / 9525
                                      let id = a.Element("xdrpic").Element("xdrblipFill").Element("ablip").Attribute("rembed").Value
                                      let name = a.Element("xdrpic").Element("xdrnvPicPr").Element("xdrcNvPr").Attribute("name").Value
                                      select new CellData
                                      {
                                          RowId = rowId,
                                          ColumnId = columnId,
                                          Width = width,
                                          Height = height,
                                          Id = id,
                                          Value = name,
                                          DataType = DataType.Picture
                                      });

                foreach (var cell in imgCellDatas)
                {
                    string key = cell.GetKey();

                    if (!keyValuePairs.ContainsKey(key))
                    {
                        keyValuePairs.Add(key, cell);
                    }
                }
                foreach (var cell in imgSingleDatas)
                {
                    string key = cell.GetKey();

                    if (!keyValuePairs.ContainsKey(key))
                    {
                        keyValuePairs.Add(key, cell);
                    }
                }
            }
            #endregion

            //excel最大行数
            int maxrow = keyValuePairs.Values.Max(row => row.RowId);

            List<T> list = new List<T>();
            Type type = typeof(T);
            var properties = type.GetProperties();
            for (int i = headRowId + 1; i <= maxrow; i++)
            {
                //根据类型创建实例
                var obj = Activator.CreateInstance(type, true);
                foreach (var property in properties)
                {
                    var columnAttr = property.GetCustomAttribute<ColumnAttribute>(true) as ColumnAttribute;

                    if (columnAttr != null
                        && headerKeyValuePairs.TryGetValue(columnAttr.ColumnName, out CellData headerCell))
                    {
                        string key = CellData.GetKey(i, headerCell.ColumnId);

                        if (keyValuePairs.TryGetValue(key, out CellData cell))
                        {
                            if (cell.DataType == DataType.Picture)
                            {
                                ImageCellData imageData = new ImageCellData
                                {
                                    OpenXmlPart = workSheetPart.DrawingsPart.GetPartById(cell.Id),
                                    Width = cell.Width,
                                    Height = cell.Height,
                                    FileName = cell.Value
                                };
                                property.SetValue(obj, imageData);
                            }
                            else
                            {
                                //赋值
                                property.SetValue(obj, Convert.ChangeType(cell.Value, property.PropertyType));
                            }
                        }
                    }
                }
                list.Add((T)obj);
            }

            return list;
        }

        /// <summary>
        /// 公共字符串集合
        /// </summary>
        /// <param name="sharedstringtablepart"></param>
        /// <returns></returns>
        public static List<string> GetShareString(this SharedStringTablePart sharedstringtablepart)
        {
            if (sharedstringtablepart == null)
                return null;

            XNamespace w = xml_namespace;

            XDocument doc = XDocument.Load(sharedstringtablepart.GetStream());

            var stringLists = new List<string>();

            var siElements = (from a in doc.Descendants(w + "si")
                              select a);

            foreach (var element in siElements)
            {
                stringLists.Add(string.Join("", (from a in element.Descendants(w + "t") select a.Value).ToArray()));
            }

            return stringLists;
            //return (from a in doc.Descendants(w + "si")
            //        from b in a.Descendants(w+"t")
            //        select b.Value).ToList();
        }

        public static XDocument GetXDocument(this OpenXmlPart part)
        {
            XDocument xdoc = part.Annotation<XDocument>();
            if (xdoc != null)
                return xdoc;
            using (StreamReader sr = new StreamReader(part.GetStream()))
            using (XmlReader xr = XmlReader.Create(sr))
                xdoc = XDocument.Load(xr);
            part.AddAnnotation(xdoc);
            return xdoc;
        }

    }

}
